<?php

//$WideSql = new mysqlHandler();

/**
 * A class for handling mysql-interaction.
 * Purpose is to automaticly clean and insert data into database.
 * Should also be able to create databases on demmand from template, and alter databases on demand to.
 * Should handle simple tasks as cleanup
 * @package WideSqlHanlder
 * 
 */
class mysqlHandler
{

    // private an non-accessible variables
    private $_rw = 2;
    private $_r = 1;
    private $_con;
    private $_username;
    private $_password;
    private $_server;
    private $_rights;
    private $_db;
    private $_error;
    public $userhandling = "disabled";

    function __construct()
    {
        $mysql_config_file = _SETTINGSPATH_ . "/mysql.ser";
        if (file_exists($mysql_config_file))
        {
            $mysql_config = unserialize(file_get_contents($mysql_config_file));

            $this->_username = $mysql_config['mysqluser'];
            $this->_password = $mysql_config['mysqlpass'];
            $this->_server = $mysql_config['mysqlserver'];
            $this->_rights = $this->_rw;
            $this->_db = $mysql_config['mysqldb'];
            $this->userhandling = $mysql_config['mysqluserhandling'];
        } else
        {
            return false;
        }
    }

    // basic private methods
    private function _connect()
    {
        if ($this->_rights < $this->_r)
        {
            $this->_error = "Du må ikke tilgå databasen";
            return false;
        }
        // Opens new connection if no connection has been made
        // returns true on succes, false on faliure - writes error to _error
        
        if (empty($this->_con))
        {
            $this->_con = @mysql_connect(
                            $this->_server,
                            $this->_username,
                            $this->_password);

            if (!$this->_con)
            {
                $this->_error = "Kunne ikke forbinde til serveren";
                return false;
            }

            $db_select = mysql_select_db($this->_db, $this->_con);
            if ($db_select)
            {
                return true;
            } else
            {
                $this->_error = "Fejl i valg af database, '$this->_db' findes ikke";
                return false;
            }
        }
        return true;
    }

    private function _queryInsert($query)
    {
        if ($this->_rights < $this->_rw)
        {
            $this->_error = "Du har ikke rettigheder til at skrive";
            return false;
        }
        // insert stuff into database - raw function, therefore private
        // Only use for resultless queries
        //  - needs public method with cleaning and table-check
        // returns true on succes, false on faliure
        if ($this->_connect())
        {
            mysql_query($query);
            return true;
        }
        $this->_error = "Fejl i forespørgslen : $query";
        return false;
    }

    private function _createReturnArray($result)
    {
        // Creates an array based upon the query, no need to go public
        while ($row = mysql_fetch_array($result))
        {
            $returnArray[] = $row;
        }
        return $returnArray;
    }

    private function _queryGet($query)
    {
        if ($this->_rights < $this->_r)
        {
            $this->_error = "Du har ikke rettigheder til at læse";
            return false;
        }
        // fetches data from mysql - should only be used when a result is expected
        // returns array on succes, false on faliure
        if ($this->_connect())
        {

            $result = mysql_query($query);
            return $this->_createReturnArray($result);
        }
        $this->_error = "Fejl i forespørgslen : $query";
        return false;
    }

    function deleteRows($table, $condition)
    {
        if (!$this->_connect())
            return false;
        $query = "DELETE FROM $table WHERE $condition";
        return $this->_queryInsert($query);
    }

    function updateRow($table, $data, $columns, $condition)
    {
        if (!$this->_connect())
            return false;
        $query = "UPDATE `$table` SET ";
        $query_end = " WHERE $condition";
        if (is_array($data) && count($data) == count($columns))
        {
            foreach ($data as $k => $d)
            {
                $q[] = "$columns[$k]='" . mysql_real_escape_string($d) . "'";
            }
            $query .= implode(", ", $q) . $query_end;
        } else
        {
            $query .= "$columns='" . mysql_real_escape_string($data) . "'" . $query_end;
        }
        return $this->_queryInsert($query);
    }

    // Class logic methods
    function insertMultipleToTable($table, $data, $columns = '')
    {
        if (!$this->_connect())
            return false;
        if (empty($table) || empty($data))
            return false;
        if (count($columns) == count($data[0]) && !empty($columns))
        {
            $cols = "(`" . implode("`, `", $columns) . "`)";
        }
        foreach ($data as &$d)
        {
            foreach ($d as &$subd)
            {
                $subd = mysql_real_escape_string($subd);
            }
            $d = "('" . implode("', '", $d) . "')";
        }
        $vals = "" . implode(", ", $data) . "";

        $query = "INSERT INTO `$this->_db`.`$table` $cols VALUES $vals;";
        $res = $this->_queryInsert($query);
        if (!$res)
            $this->_error = "Fejl ved forsøg på indsæt, query: $query";
        return $res;
    }

    function numberOfRecords($table)
    {
        $count = $this->_queryGet("SELECT COUNT(*) FROM $table");
        return $count[0][0];
    }

    function insertToTable($table, $data, $columns = '')
    {
        if (!$this->_connect())
            return false;
        if (empty($table) || empty($data))
            return false;
        if (count($columns) == count($data) && !empty($columns))
        {
            $cols = "(`" . implode("`, `", $columns) . "`)";
        }
        foreach ($data as &$d)
        {
            $d = mysql_real_escape_string($d);
        }
        $vals = "('" . implode("', '", $data) . "')";

        $query = "INSERT INTO `$this->_db`.`$table` $cols VALUES $vals;";
        $res = $this->_queryInsert($query);
        if (!$res)
            $this->_error = "Fejl ved forsøg på indsæt, query: $query";
        return $res;
    }

    function getFromTable($table, $condition = NULL, $wanted = NULL, $sort = NULL, $order = NULL, $limit = NULL)
    {
        if (!$this->_connect())
            return false;
        // defaulting
        if (is_array($wanted))
        {
            $wanted = "`" . implode("`, `", $wanted) . "`";
        } else
        {
            $wanted = is_null($wanted) ? "*" : "`" . $wanted . "`";
        }
        if (!is_null($sort))
        {
            $sort = "ORDER BY `" . $sort . "` ";
            $sort = is_null($order) ? $sort . "DESC" : $sort . $order;
        }
        $limit = is_null($limit) ? NULL : "LIMIT " . implode(",", $limit);
        $condition = is_null($condition) ? '' : "WHERE $condition";
        $query = "SELECT $wanted FROM  `$table` $condition $sort $limit";
        // end defaulting
        return $this->_queryGet($query);
    }

    // Config methods
    function testConnection()
    {
        // public method for connectiontest
        // should have a coresponding bool method to
        if ($this->_connect())
        {
            return "Der er oprettet forbindelse til MYSQL";
        } else
        {
            return "Der er en fejl i configurationen : $this->_error";
        }
    }

    function boolTestConnection()
    {
        if ($this->_connect())
        {
            return true;
        } else
        {
            return false;
        }
    }

    function allTables()
    {
        if (!$this->_connect())
            return false;
        // shows all tables i current DB, with fieldinfo - returns array
        $raw = $this->_queryGet("SHOW TABLES IN $this->_db");
        $tables = array();
        foreach ($raw as $table)
        {
            $tables[$table[0]] = $this->_queryGet("SHOW COLUMNS FROM $table[0] IN $this->_db");
        }
        return $tables;
    }

    function getError()
    {
        // get the error
        return $this->_error;
    }

    function __destruct()
    {
        // closes connection when object is killed
        @mysql_close($this->_con);
    }

}
?>
